<!DOCTYPE html>
<html lang="en">





<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.jpg">
  <link rel="icon" type="image/png" href="/img/favicon.jpg">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="">
  <meta name="author" content="John Doe">
  <meta name="keywords" content="">
  <title>MySQL基础 - Nyima&#39;s Blog</title>

  <link  rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.staticfile.org/github-markdown-css/4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    <link  rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/10.0.0/styles/github-gist.min.css" />
  

  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_yg9cfy8wd6.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_pjno9b9zyxs.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script  src="/js/utils.js" ></script>
<meta name="generator" content="Hexo 4.2.1"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/">&nbsp;<strong>Nyima</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                Home
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                Archives
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                Categories
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                Tags
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/links/">
                <i class="iconfont icon-link-fill"></i>
                Links
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                About
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="view intro-2" id="background" parallax=true
         style="background: url('/img/3.jpg') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="container text-center white-text fadeInUp">
            <span class="h2" id="subtitle">
              
            </span>

            
              
  <div class="mt-3 post-meta">
    <i class="iconfont icon-date-fill" aria-hidden="true"></i>
    <time datetime="2020-08-11 10:00">
      August 11, 2020 am
    </time>
  </div>


<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      4.1k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      58
       分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid">
  <div class="row">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-md">
      <div class="container nopadding-md" id="board-ctn">
        <div class="py-5" id="board">
          <div class="post-content mx-auto" id="post">
            
            <article class="markdown-body">
              <h1 id="MySQL基础学习"><a href="#MySQL基础学习" class="headerlink" title="MySQL基础学习"></a>MySQL基础学习</h1><h1 id="一、基本操作"><a href="#一、基本操作" class="headerlink" title="一、基本操作"></a>一、基本操作</h1><h2 id="1、创建数据库和表"><a href="#1、创建数据库和表" class="headerlink" title="1、创建数据库和表"></a>1、创建数据库和表</h2><p>创建数据库语句</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span> <span class="hljs-keyword">database</span>;</code></pre>

<p>展示所有数据库</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">DATABASES</span>;</code></pre>

<p>使用指定数据库</p>
<pre><code class="hljs sql"><span class="hljs-keyword">USE</span> <span class="hljs-keyword">database</span>;</code></pre>



<p>创建表</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> student(
    s_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) PRIMARY <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生学号'</span>,
    s_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生姓名 不能为空'</span>,
    s_sex <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生性别'</span>,
    s_birthday DATETIME <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生生日'</span>,
    s_class <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生所在的班级'</span>
);</code></pre>

<p>展示表</p>
<pre><code class="hljs sql"><span class="hljs-keyword">DESCRIBE</span> <span class="hljs-keyword">table</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200807154719.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="2、对表的操作"><a href="#2、对表的操作" class="headerlink" title="2、对表的操作"></a>2、对表的操作</h2><h3 id="查看操作"><a href="#查看操作" class="headerlink" title="查看操作"></a>查看操作</h3><p>查看表中的记录</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> *<span class="hljs-keyword">FROM</span> <span class="hljs-keyword">table</span>;</code></pre>



<h3 id="插入操作"><a href="#插入操作" class="headerlink" title="插入操作"></a>插入操作</h3><pre><code class="hljs sql"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">''</span>, <span class="hljs-string">''</span>...);</code></pre>



<h3 id="删除操作"><a href="#删除操作" class="headerlink" title="删除操作"></a>删除操作</h3><pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">WHERE</span> cow = <span class="hljs-string">''</span>;</code></pre>



<h3 id="修改数据"><a href="#修改数据" class="headerlink" title="修改数据"></a>修改数据</h3><pre><code class="hljs sql"><span class="hljs-keyword">UPDATE</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">SET</span> cow1 = <span class="hljs-string">''</span> <span class="hljs-keyword">WHERE</span> cow2 = <span class="hljs-string">''</span>;</code></pre>



<h1 id="二、约束"><a href="#二、约束" class="headerlink" title="二、约束"></a>二、约束</h1><h2 id="1、主键"><a href="#1、主键" class="headerlink" title="1、主键"></a>1、主键</h2><p>能够唯一确定表中的一条记录。我们通过给某个字段添加该约束，就可以使得该字段不重复且不为空。</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">user</span> (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)
);</code></pre>



<h3 id="联合主键"><a href="#联合主键" class="headerlink" title="联合主键"></a>联合主键</h3><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> user2 (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
    PRIMARY <span class="hljs-keyword">KEY</span>(<span class="hljs-keyword">id</span>, <span class="hljs-keyword">name</span>)
);</code></pre>

<p>此处字段id和name一同作为主键，联合主键要求每个字段<strong>加起来不同即可</strong>（无需每个字段都不同）</p>
<h3 id="建表后添加主键"><a href="#建表后添加主键" class="headerlink" title="建表后添加主键"></a>建表后添加主键</h3><pre><code class="hljs sql">ALERT TABLE user2 ADD PRIMARY KEY (id);</code></pre>

<p>或者通过<strong>修改字段</strong>的方式来添加主键</p>
<pre><code class="hljs sql">ALERT TABLE user2 MODIFY id INT PRIMARY KEY;</code></pre>



<h3 id="建表后删除主键"><a href="#建表后删除主键" class="headerlink" title="建表后删除主键"></a>建表后删除主键</h3><pre><code class="hljs sql">ALERT TABLE user2 <span class="hljs-keyword">DROP</span>  PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-keyword">id</span>);</code></pre>



<h2 id="2、自增约束"><a href="#2、自增约束" class="headerlink" title="2、自增约束"></a>2、自增约束</h2><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> user3 (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span> AUTO_INCREAMENT,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
);

<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> user3 (<span class="hljs-keyword">name</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'name'</span>); //插入成功，自动生成了id</code></pre>

<p>自增约束一般<strong>与主键搭配使用</strong></p>
<h2 id="3、唯一约束"><a href="#3、唯一约束" class="headerlink" title="3、唯一约束"></a>3、唯一约束</h2><p>约束修饰的字段不可以重复</p>
<pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> user4 (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span> AUTO_INCREAMENT <span class="hljs-keyword">UNIQUE</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)
);</code></pre>

<p>或者</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> user4 (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span> AUTO_INCREAMENT,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	<span class="hljs-keyword">UNIQUE</span>(<span class="hljs-keyword">id</span>, <span class="hljs-keyword">name</span>)
);</code></pre>



<h2 id="4、非空约束"><a href="#4、非空约束" class="headerlink" title="4、非空约束"></a>4、非空约束</h2><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> user4 (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
);</code></pre>



<h2 id="5、默认约束"><a href="#5、默认约束" class="headerlink" title="5、默认约束"></a>5、默认约束</h2><pre><code class="hljs SQL"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> user4 (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">10</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
);</code></pre>

<p>如果我们插入字段时没有传值，就会<strong>使用默认值</strong></p>
<h2 id="6、外键约束"><a href="#6、外键约束" class="headerlink" title="6、外键约束"></a>6、外键约束</h2><p>主表</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">master</span>(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)
);</code></pre>

<p>从表</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> pet(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	master_id <span class="hljs-built_in">int</span>,
	<span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span>(master_id) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-keyword">master</span>(<span class="hljs-keyword">id</span>)
);</code></pre>

<ul>
<li>主表中没有的数据，在副表中是不可以使用的</li>
<li>主表中的数据被副标引用时，是不可以删除的</li>
</ul>
<h1 id="三、数据库的三大范式"><a href="#三、数据库的三大范式" class="headerlink" title="三、数据库的三大范式"></a>三、数据库的三大范式</h1><h2 id="1、第一范式"><a href="#1、第一范式" class="headerlink" title="1、第一范式"></a>1、第一范式</h2><p><strong>字段还可以拆分的，就不满足第一范式</strong></p>
<p>比如地址如果写为</p>
<pre><code class="hljs plain">地址：四川省成都市高新区天府一街</code></pre>

<p>就是可以被拆分的</p>
<p>如果字段写为</p>
<pre><code class="hljs plain">省份：四川省
城市：成都市
区域：高新区
街名：天府一街</code></pre>

<p>就是不可拆分的</p>
<p>建表如下</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> student(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span> AUTO_INCRAEMENT,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	province <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	city <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	area <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	street: <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)
);</code></pre>

<p>就是<strong>符合第一范式</strong>的，但<strong>并不是拆分的越详细越好</strong></p>
<h2 id="2、第二范式"><a href="#2、第二范式" class="headerlink" title="2、第二范式"></a>2、第二范式</h2><ul>
<li><p>满足第一范式的条件下，第二范式要求：<strong>除主键外的每一列，都必须完全依赖于主键</strong></p>
</li>
<li><p>如果出现不完全依赖，则只可能发生在<strong>联合主键</strong>的情况下</p>
</li>
</ul>
<p><strong>不满足第二范式的例子</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">order</span>(
	product_id <span class="hljs-built_in">INT</span>,
	customer_id <span class="hljs-built_in">INT</span>,
	product_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	customer_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
    PRIMARY <span class="hljs-keyword">KEY</span>(product_id, customer_id)
);</code></pre>

<p>此处product_name只依赖于product_id，customer_name只依赖于customer_id，是完全依赖</p>
<p><strong>满足第二范式的例子</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">order</span>(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
	product_id <span class="hljs-built_in">INT</span>,
	customer_id <span class="hljs-built_in">INT</span>
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> product(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
	<span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> customer(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
    <span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)
);</code></pre>



<h2 id="3、第三范式"><a href="#3、第三范式" class="headerlink" title="3、第三范式"></a>3、第三范式</h2><ul>
<li>满足第二范式，除主键外的其他列之间不能有传递依赖关系</li>
</ul>
<p><strong>不满足第三范式的例子</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">order</span>(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
	product_id <span class="hljs-built_in">INT</span>,
	customer_id <span class="hljs-built_in">INT</span>,
	customer_phone <span class="hljs-built_in">INT</span>
);</code></pre>

<p>此处customer_phone又依赖于customer_id，存在传递依赖关系，不满足第三范式</p>
<p><strong>满足第三范式的例子</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">order</span>(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
	product_id <span class="hljs-built_in">INT</span>,
	customer_id <span class="hljs-built_in">INT</span>
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> customer(
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span> PRIMARY <span class="hljs-keyword">KEY</span>,
    <span class="hljs-keyword">name</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
    phone <span class="hljs-built_in">INT</span>
);</code></pre>



<h1 id="四、查询练习"><a href="#四、查询练习" class="headerlink" title="四、查询练习"></a>四、查询练习</h1><p>建表语句及插入语句</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> student(
    s_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) PRIMARY <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生学号'</span>,
    s_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生姓名 不能为空'</span>,
    s_sex <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生性别'</span>,
    s_birthday DATETIME <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生生日'</span>,
    s_class <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'学生所在的班级'</span>
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> teacher(
    t_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) PRIMARY <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'教师编号'</span>,
    t_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'教师姓名'</span>,
    t_sex <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'教师性别'</span>,
    t_birthday DATETIME <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'教师生日'</span>,
    t_rof <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'教师职称'</span>,
    t_depart <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'教师所在的部门'</span>
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> course(
    c_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) PRIMARY <span class="hljs-keyword">KEY</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'课程号'</span>,
    c_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'课程名称'</span>,
    t_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'教师编号 外键关联teacher表'</span>,
    <span class="hljs-keyword">FOREIGN</span> <span class="hljs-keyword">KEY</span>(t_no) <span class="hljs-keyword">references</span> teacher(t_no)
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> score (
    s_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'成绩表的编号 依赖学生学号'</span>,
        c_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)  <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">COMMENT</span><span class="hljs-string">'课程号 依赖于课程表中的c_id'</span>,
    sc_degree <span class="hljs-built_in">decimal</span>,
    <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span>(s_no) <span class="hljs-keyword">references</span> student(s_no),
    <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span>(c_no) <span class="hljs-keyword">references</span> course(c_no),
    PRIMARY <span class="hljs-keyword">KEY</span>(s_no,c_no)
);</code></pre>

<pre><code class="hljs sql"><span class="hljs-comment">--学生表数据</span>
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'101'</span>,<span class="hljs-string">'曾华'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1977-09-01'</span>,<span class="hljs-string">'95033'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'102'</span>,<span class="hljs-string">'匡明'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1975-10-02'</span>,<span class="hljs-string">'95031'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'103'</span>,<span class="hljs-string">'王丽'</span>,<span class="hljs-string">'女'</span>,<span class="hljs-string">'1976-01-23'</span>,<span class="hljs-string">'95033'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'104'</span>,<span class="hljs-string">'李军'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1976-02-20'</span>,<span class="hljs-string">'95033'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'105'</span>,<span class="hljs-string">'王芳'</span>,<span class="hljs-string">'女'</span>,<span class="hljs-string">'1975-02-10'</span>,<span class="hljs-string">'95031'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'106'</span>,<span class="hljs-string">'陆军'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1974-06-03'</span>,<span class="hljs-string">'95031'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'107'</span>,<span class="hljs-string">'王尼玛'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1976-02-20'</span>,<span class="hljs-string">'95033'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'108'</span>,<span class="hljs-string">'张全蛋'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1975-02-10'</span>,<span class="hljs-string">'95031'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> student <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'109'</span>,<span class="hljs-string">'赵铁柱'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1974-06-03'</span>,<span class="hljs-string">'95031'</span>);

<span class="hljs-comment">--教师表数据</span>
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> teacher <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'804'</span>,<span class="hljs-string">'李诚'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1958-12-02'</span>,<span class="hljs-string">'副教授'</span>,<span class="hljs-string">'计算机系'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> teacher <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'856'</span>,<span class="hljs-string">'张旭'</span>,<span class="hljs-string">'男'</span>,<span class="hljs-string">'1969-03-12'</span>,<span class="hljs-string">'讲师'</span>,<span class="hljs-string">'电子工程系'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> teacher <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'825'</span>,<span class="hljs-string">'王萍'</span>,<span class="hljs-string">'女'</span>,<span class="hljs-string">'1972-05-05'</span>,<span class="hljs-string">'助教'</span>,<span class="hljs-string">'计算机系'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> teacher <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'831'</span>,<span class="hljs-string">'刘冰'</span>,<span class="hljs-string">'女'</span>,<span class="hljs-string">'1977-08-14'</span>,<span class="hljs-string">'助教'</span>,<span class="hljs-string">'电子工程系'</span>);

<span class="hljs-comment">--添加课程表</span>
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> course <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'3-105'</span>,<span class="hljs-string">'计算机导论'</span>,<span class="hljs-string">'825'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> course <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'3-245'</span>,<span class="hljs-string">'操作系统'</span>,<span class="hljs-string">'804'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> course <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'6-166'</span>,<span class="hljs-string">'数字电路'</span>,<span class="hljs-string">'856'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> course <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'9-888'</span>,<span class="hljs-string">'高等数学'</span>,<span class="hljs-string">'831'</span>);

<span class="hljs-comment">--添加成绩表</span>
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'103'</span>,<span class="hljs-string">'3-245'</span>,<span class="hljs-string">'86'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'105'</span>,<span class="hljs-string">'3-245'</span>,<span class="hljs-string">'75'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'109'</span>,<span class="hljs-string">'3-245'</span>,<span class="hljs-string">'68'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'103'</span>,<span class="hljs-string">'3-105'</span>,<span class="hljs-string">'92'</span>);

<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'105'</span>,<span class="hljs-string">'3-105'</span>,<span class="hljs-string">'88'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'109'</span>,<span class="hljs-string">'3-105'</span>,<span class="hljs-string">'76'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'103'</span>,<span class="hljs-string">'6-166'</span>,<span class="hljs-string">'85'</span>);

<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'105'</span>,<span class="hljs-string">'6-166'</span>,<span class="hljs-string">'79'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'109'</span>,<span class="hljs-string">'6-166'</span>,<span class="hljs-string">'81'</span>);</code></pre>



<ul>
<li>查询student表中所有记录的s_name,s_sex和s_class列</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_no,s_name,s_class <span class="hljs-keyword">FROM</span>  student;</code></pre>



<ul>
<li>查询教师所有的单位但是不重复的t_depart列</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">DISTINCT</span>(t_depart) <span class="hljs-keyword">FROM</span> teacher;</code></pre>



<ul>
<li>查询score表中成绩在60-80之间所有的记录(sc_degree)</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_no, sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> sc_degree <span class="hljs-keyword">BETWEEN</span> <span class="hljs-number">60</span> <span class="hljs-keyword">AND</span> <span class="hljs-number">80</span>;
<span class="hljs-keyword">SELECT</span> s_no, sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> sc_degree &gt; <span class="hljs-number">60</span> <span class="hljs-keyword">AND</span> sc_degree &lt; <span class="hljs-number">80</span>;</code></pre>



<ul>
<li>查询score表中成绩为85, 86, 或者88的记录(sc_degree)</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_no, sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> sc_degree <span class="hljs-keyword">IN</span>(<span class="hljs-number">85</span>, <span class="hljs-number">86</span>, <span class="hljs-number">88</span>);</code></pre>



<ul>
<li>查询student表中’95031’班或者性别为’女’的同学记录</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> *<span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_class = <span class="hljs-string">'95031'</span> <span class="hljs-keyword">OR</span> s_sex = <span class="hljs-string">'女'</span>;</code></pre>



<ul>
<li>以sc_degree降序查询score表中所有的记录</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_no, sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> sc_degree <span class="hljs-keyword">DESC</span>;</code></pre>



<ul>
<li>查询’95031’班的学生人数</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">COUNT</span>(*) <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_class = <span class="hljs-string">'95031'</span>;</code></pre>



<ul>
<li>查询score表中的最高分数的学生号和课程号</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_no, c_no <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> sc_degree = (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">MAX</span>(sc_degree) <span class="hljs-keyword">FROM</span> score);</code></pre>



<ul>
<li>查询每门课的平均成绩</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> c_no, <span class="hljs-keyword">AVG</span>(sc_degree) <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_no;</code></pre>



<ul>
<li>查询score表中至少有2名学生选修的,并且以3开头的课程的平均分</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> c_no, <span class="hljs-keyword">AVG</span>(sc_degree) <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_no <span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">COUNT</span>(c_no) &gt;=<span class="hljs-number">2</span> <span class="hljs-keyword">AND</span> c_no <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'3%'</span>;</code></pre>



<ul>
<li>查询所有的学生 s_name , c_no, sc_degree列</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_name, c_no, sc_degree <span class="hljs-keyword">FROM</span> student, score <span class="hljs-keyword">WHERE</span> student.s_no = score.s_no;</code></pre>



<ul>
<li>查询所有学生的s_no, c_name, sc_degree列</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_no, c_name, sc_degree <span class="hljs-keyword">FROM</span> student, score, course <span class="hljs-keyword">WHERE</span> student.s_no = score.s_no <span class="hljs-keyword">AND</span> score.c_no = course.c_no;</code></pre>



<ul>
<li>查询所有的学生 s_name , c_name, sc_degree列</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_name, c_name, sc_degree <span class="hljs-keyword">FROM</span> student, score, course <span class="hljs-keyword">WHERE</span> student.s_no = score.s_no <span class="hljs-keyword">AND</span> score.c_no = course.c_no;</code></pre>



<ul>
<li>查询班级是’95031’班学生每门课的平均分</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> c_no, <span class="hljs-keyword">AVG</span>(sc_degree) <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> s_no <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> s_no <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_class = <span class="hljs-string">'95031'</span>) <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span>(c_no);

<span class="hljs-keyword">SELECT</span> c_no, <span class="hljs-keyword">AVG</span>(sc_degree) <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">AS</span> s <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> score <span class="hljs-keyword">AS</span> sc <span class="hljs-keyword">ON</span> s.s_no = sc.s_no <span class="hljs-keyword">WHERE</span> s_class = <span class="hljs-string">'95031'</span> <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_no;

<span class="hljs-comment">--查询内容包含课程名（c_name）</span>
<span class="hljs-keyword">SELECT</span> c.c_no, c.c_name, <span class="hljs-keyword">AVG</span>(sc.sc_degree) <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">AS</span> s, course <span class="hljs-keyword">AS</span> c, score <span class="hljs-keyword">AS</span> sc <span class="hljs-keyword">WHERE</span> s.s_no = sc.s_no <span class="hljs-keyword">AND</span> sc.c_no = c.c_no <span class="hljs-keyword">AND</span> s.s_class = <span class="hljs-string">'95031'</span>  <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c.c_no;</code></pre>



<ul>
<li>查询选修”3-105”课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> *<span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">AS</span> s, score <span class="hljs-keyword">AS</span> sc <span class="hljs-keyword">WHERE</span> sc.sc_degree &gt; (<span class="hljs-keyword">SELECT</span> sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> s_no = <span class="hljs-string">'109'</span> <span class="hljs-keyword">AND</span> c_no = <span class="hljs-string">'3-105'</span>) <span class="hljs-keyword">AND</span> sc.c_no = <span class="hljs-string">'3-105'</span> <span class="hljs-keyword">AND</span> s.s_no = sc.s_no;</code></pre>



<ul>
<li>查询所有与学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> *<span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">YEAR</span>(s_birthday) <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">YEAR</span>(s_birthday) <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_no <span class="hljs-keyword">IN</span> (<span class="hljs-string">'101'</span>, <span class="hljs-string">'108'</span>));</code></pre>



<ul>
<li>查询 张旭 教师任课的学生的成绩</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s.s_name, sc.sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">AS</span> sc, student <span class="hljs-keyword">AS</span> s <span class="hljs-keyword">WHERE</span> c_no = (<span class="hljs-keyword">SELECT</span> c_no <span class="hljs-keyword">FROM</span> course <span class="hljs-keyword">WHERE</span> t_no = (<span class="hljs-keyword">SELECT</span> t_no <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_name = <span class="hljs-string">'张旭'</span>)) <span class="hljs-keyword">AND</span> s.s_no = sc.s_no;</code></pre>



<ul>
<li>查询95033班和95031班全体学生的记录</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_class <span class="hljs-keyword">IN</span> (<span class="hljs-string">'95033'</span>, <span class="hljs-string">'95031'</span>) <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> s_class;</code></pre>



<ul>
<li>查询存在85分以上成绩的c_name和对应的老师</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> t_name, c_name <span class="hljs-keyword">FROM</span> course <span class="hljs-keyword">AS</span> c, teacher <span class="hljs-keyword">AS</span> t <span class="hljs-keyword">WHERE</span> c_no <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> c_no <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> sc_degree &gt; <span class="hljs-number">85</span>) <span class="hljs-keyword">AND</span> c.t_no = t.t_no;</code></pre>



<ul>
<li>查出所有’计算机系’ 教师所教课程的教师信息、课程信息及学生信息</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span>  * <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">AS</span> t, course <span class="hljs-keyword">AS</span> c, student <span class="hljs-keyword">AS</span> s, score <span class="hljs-keyword">AS</span> sc 
<span class="hljs-keyword">WHERE</span> s.s_no = sc.s_no 
<span class="hljs-keyword">AND</span> c.t_no = t.t_no 
<span class="hljs-keyword">AND</span> sc.c_no = c.c_no 
<span class="hljs-keyword">AND</span> t.t_no <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> t_no <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_depart = <span class="hljs-string">'计算机系'</span>);</code></pre>



<ul>
<li>查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_depart = <span class="hljs-string">'计算机系'</span> 
<span class="hljs-keyword">AND</span> t_rof <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> t_rof <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_depart = <span class="hljs-string">'电子工程系'</span>)
<span class="hljs-keyword">UNION</span> 
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_depart = <span class="hljs-string">'电子工程系'</span> 
<span class="hljs-keyword">AND</span> t_rof <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> t_rof <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_depart = <span class="hljs-string">'计算机系'</span>);</code></pre>



<ul>
<li>查询选修编号为”3-105”课程且成绩<strong>至少</strong>高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> c_no = <span class="hljs-string">'3-105'</span> <span class="hljs-keyword">AND</span> sc_degree &gt; <span class="hljs-keyword">ANY</span>(<span class="hljs-keyword">SELECT</span> sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> c_no = <span class="hljs-string">'3-245'</span>) <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> sc_degree <span class="hljs-keyword">DESC</span>;</code></pre>



<ul>
<li>查询选修编号为”3-105”且成绩高于选修编号为”3-245”课程的同学c_no.s_no和sc_degree</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> c_no = <span class="hljs-string">'3-105'</span> 
<span class="hljs-keyword">AND</span> sc_degree &gt; <span class="hljs-keyword">ALL</span>(<span class="hljs-keyword">SELECT</span> sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> c_no = <span class="hljs-string">'3-245'</span>)
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> sc_degree <span class="hljs-keyword">DESC</span>;</code></pre>



<ul>
<li>查出学生的信息,课程名称,分数(s_name c_name,sc_degree)</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s.s_name, c.c_name, sc.sc_degree <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">AS</span> s, course <span class="hljs-keyword">AS</span> c, score <span class="hljs-keyword">AS</span> sc
<span class="hljs-keyword">WHERE</span> s.s_no = sc.s_no
<span class="hljs-keyword">AND</span> c.c_no = sc.c_no
<span class="hljs-keyword">AND</span> sc.sc_degree &gt; <span class="hljs-keyword">ALL</span>(<span class="hljs-keyword">SELECT</span> sc_degree <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> c_no = <span class="hljs-string">'3-245'</span>)
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> sc_degree <span class="hljs-keyword">DESC</span>;</code></pre>



<p>总结: </p>
<ul>
<li>ANY 和 ALL<ul>
<li>ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了</li>
<li>ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行</li>
</ul>
</li>
</ul>
<ul>
<li>查询所有教师和同学的 name ,sex, birthday</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_name, s_sex, s_birthday <span class="hljs-keyword">FROM</span> student
<span class="hljs-keyword">UNION</span>
<span class="hljs-keyword">SELECT</span> t_name, t_sex, t_birthday <span class="hljs-keyword">FROM</span> teacher;</code></pre>



<ul>
<li>查询所有’女’教师和’女’学生的name,sex,birthday</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_name, s_sex, s_birthday <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_sex = <span class="hljs-string">'女'</span>
<span class="hljs-keyword">UNION</span>
<span class="hljs-keyword">SELECT</span> t_name, t_sex, t_birthday <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_sex = <span class="hljs-string">'女'</span>;</code></pre>



<ul>
<li>查询成绩比该课程平均成绩低的同学的成绩表</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">AS</span> a <span class="hljs-keyword">WHERE</span> sc_degree &lt; (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">AVG</span>(sc_degree) <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">AS</span> b <span class="hljs-keyword">WHERE</span> a.c_no = b.c_no);</code></pre>

<ul>
<li>查询成绩比该课程平均成绩低的同学的成绩表，并显示出学生name,课程name以及分数</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s.s_name <span class="hljs-keyword">AS</span> 学生姓名, c.c_name <span class="hljs-keyword">AS</span> 课程名, a.sc_degree <span class="hljs-keyword">AS</span> 成绩  <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">AS</span> a, student <span class="hljs-keyword">AS</span> s, course <span class="hljs-keyword">AS</span> c 
<span class="hljs-keyword">WHERE</span> sc_degree &lt; (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">AVG</span>(sc_degree) <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">AS</span> b <span class="hljs-keyword">WHERE</span> a.c_no = b.c_no)
<span class="hljs-keyword">AND</span> s.s_no = a.s_no
<span class="hljs-keyword">AND</span> c.c_no = a.c_no;</code></pre>



<ul>
<li>查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">WHERE</span> t_no <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> t_no <span class="hljs-keyword">FROM</span> course <span class="hljs-keyword">WHERE</span> c_no <span class="hljs-keyword">IN</span> (<span class="hljs-keyword">SELECT</span> c_no <span class="hljs-keyword">FROM</span> score));</code></pre>



<ul>
<li>查出至少有2名男生的班号</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_class <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_sex = <span class="hljs-string">'男'</span> 
<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> s_class <span class="hljs-keyword">HAVING</span> <span class="hljs-keyword">COUNT</span>(s_no) &gt; <span class="hljs-number">1</span>;</code></pre>



<ul>
<li>查询student 表中 不姓”王”的同学的记录</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_name <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'王%'</span>;</code></pre>



<ul>
<li>查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_name <span class="hljs-keyword">AS</span> 姓名, (<span class="hljs-keyword">YEAR</span>(<span class="hljs-keyword">NOW</span>())-<span class="hljs-keyword">YEAR</span>(s_birthday)) <span class="hljs-keyword">AS</span> 年龄 <span class="hljs-keyword">FROM</span> student;</code></pre>



<ul>
<li>查询student中最大和最小的 s_birthday的值</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">MAX</span>(s_birthday), <span class="hljs-keyword">MIN</span>(s_birthday) <span class="hljs-keyword">FROM</span> student;</code></pre>



<ul>
<li>以班级号和年龄从大到小的顺序查询student表中的全部记录</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> s_class <span class="hljs-keyword">DESC</span>, (<span class="hljs-keyword">YEAR</span>(<span class="hljs-keyword">NOW</span>()) - <span class="hljs-keyword">YEAR</span>(s_birthday)) <span class="hljs-keyword">DESC</span>;</code></pre>



<ul>
<li>查询”男”教师 及其所上的课</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> t.t_name, c.c_name <span class="hljs-keyword">FROM</span> teacher <span class="hljs-keyword">AS</span> t, course <span class="hljs-keyword">AS</span> c <span class="hljs-keyword">WHERE</span> t.t_sex = <span class="hljs-string">'男'</span>  <span class="hljs-keyword">AND</span> t.t_no = c.t_no;</code></pre>



<ul>
<li>查询最高分同学的s_no c_no 和 sc_degree;</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_no = (<span class="hljs-keyword">SELECT</span> s_no <span class="hljs-keyword">FROM</span> score <span class="hljs-keyword">WHERE</span> sc_degree  = (<span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">MAX</span>(sc_degree) <span class="hljs-keyword">FROM</span> score));</code></pre>



<ul>
<li>查询和”李军”同性别的所有同学的s_name</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_name <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_sex =  (<span class="hljs-keyword">SELECT</span> s_sex <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_name = <span class="hljs-string">'李军'</span>);</code></pre>



<ul>
<li>查询和”李军”同性别并且同班的所有同学的s_name</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s_name <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_sex =  (<span class="hljs-keyword">SELECT</span> s_sex <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_name = <span class="hljs-string">'李军'</span>)
<span class="hljs-keyword">AND</span> s_class = (<span class="hljs-keyword">SELECT</span> s_class <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">WHERE</span> s_name = <span class="hljs-string">'李军'</span>);</code></pre>



<ul>
<li>查询所有选修’计算机导论’课程的’男’同学的成绩表，并显示出s_name,c_name</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> s.s_name, c.c_name, sc.sc_degree <span class="hljs-keyword">FROM</span> student <span class="hljs-keyword">AS</span> s, score <span class="hljs-keyword">AS</span> sc, course <span class="hljs-keyword">AS</span> c 
<span class="hljs-keyword">WHERE</span> s.s_no = sc.s_no <span class="hljs-keyword">AND</span> c.c_no = sc.c_no 
<span class="hljs-keyword">AND</span> c.c_name = <span class="hljs-string">'计算机导论'</span>
<span class="hljs-keyword">AND</span> s.s_sex = <span class="hljs-string">'男'</span>;</code></pre>



<h1 id="五、连接查询"><a href="#五、连接查询" class="headerlink" title="五、连接查询"></a>五、连接查询</h1><p>建表语句</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> person (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>,
	<span class="hljs-keyword">NAME</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>),
	card_id <span class="hljs-built_in">INT</span>
);

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> card (
	<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>,
	<span class="hljs-keyword">NAME</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>)
);

<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> card <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">1</span>, <span class="hljs-string">'饭卡'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> card <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">2</span>, <span class="hljs-string">'建行卡'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> card <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">3</span>, <span class="hljs-string">'工商卡'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> card <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">4</span>, <span class="hljs-string">'农行卡'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> card <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">5</span>, <span class="hljs-string">'邮政卡'</span>);

<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> person <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">1</span>, <span class="hljs-string">'张三'</span>, <span class="hljs-number">1</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> person <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">2</span>, <span class="hljs-string">'李四'</span>, <span class="hljs-number">3</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> person <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">3</span>, <span class="hljs-string">'王五'</span>, <span class="hljs-number">6</span>);</code></pre>



<p><strong>没有创建外键时，可以使用内外查询将两个表的内容合并在一起查询</strong></p>
<h2 id="1、内连接"><a href="#1、内连接" class="headerlink" title="1、内连接"></a>1、内连接</h2><pre><code class="hljs SQL">INNER JOIN ... ON ...</code></pre>

<p>举例：</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> person <span class="hljs-keyword">AS</span> p <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> card  <span class="hljs-keyword">AS</span> c <span class="hljs-keyword">ON</span> p.card_id = c.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810195054.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>内联查询：两张表通过某个字段合并起来，查询出相关数据</strong></p>
<h2 id="2、外连接"><a href="#2、外连接" class="headerlink" title="2、外连接"></a>2、外连接</h2><h3 id="左外连接"><a href="#左外连接" class="headerlink" title="左外连接"></a>左外连接</h3><pre><code class="hljs sql">LEFT JOIN ... ON ...
或
LEFT OUTER JOIN ... ON ...</code></pre>

<p>举例：</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> person <span class="hljs-keyword">AS</span> p <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> card <span class="hljs-keyword">AS</span> c <span class="hljs-keyword">ON</span> p.card_id = c.id;
或者
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> person <span class="hljs-keyword">AS</span> p <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">OUTER</span> <span class="hljs-keyword">JOIN</span> card <span class="hljs-keyword">AS</span> c <span class="hljs-keyword">ON</span> p.card_id = c.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810195219.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>左外连接：</strong></p>
<ul>
<li><p>会把左表里的所有数据都取出来</p>
</li>
<li><p>对于右表，如果有数据就取出，如果没有就为空</p>
</li>
</ul>
<h3 id="右外连接"><a href="#右外连接" class="headerlink" title="右外连接"></a>右外连接</h3><pre><code class="hljs lasso">RIGHT <span class="hljs-keyword">JOIN</span> <span class="hljs-params">...</span> <span class="hljs-keyword">ON</span> <span class="hljs-params">...</span>
或
RIGHT OUTER <span class="hljs-keyword">JOIN</span> <span class="hljs-params">...</span> <span class="hljs-keyword">ON</span> <span class="hljs-params">...</span></code></pre>

<p>举例：</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> person <span class="hljs-keyword">AS</span> p <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span> card <span class="hljs-keyword">AS</span> c <span class="hljs-keyword">ON</span> p.card_id = c.id;
或
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> person <span class="hljs-keyword">AS</span> p <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">OUTER</span> <span class="hljs-keyword">JOIN</span> card <span class="hljs-keyword">AS</span> c <span class="hljs-keyword">ON</span> p.card_id = c.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810195526.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>右外连接</strong></p>
<ul>
<li>会把右表中的所有数据都取出来</li>
<li>对于左表，如果有数据就取出，如果没有就为空</li>
</ul>
<h2 id="3、三种连接的图解"><a href="#3、三种连接的图解" class="headerlink" title="3、三种连接的图解"></a>3、三种连接的图解</h2><p><strong>内连接</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810200025.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>左连接</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810200040.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>右连接</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810200104.png" srcset="/img/loading.gif" alt=""></p>
<h1 id="六、事务"><a href="#六、事务" class="headerlink" title="六、事务"></a>六、事务</h1><p>事务是一个最小的不可分割的单元，事务能够保证一个业务的完整性</p>
<p>多条sql语句<strong>要么同时成功，要么同时失败</strong>，这时就要用到事务</p>
<h2 id="1、自动提交、手动提交和回滚"><a href="#1、自动提交、手动提交和回滚" class="headerlink" title="1、自动提交、手动提交和回滚"></a>1、自动提交、手动提交和回滚</h2><p>通过</p>
<pre><code class="hljs sql"><span class="hljs-keyword">ROLLBACK</span>;</code></pre>

<p>指令可以回滚，但需要关闭自动提交</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SET</span> autocommit = <span class="hljs-number">0</span>
查看自动提交状态
<span class="hljs-keyword">SELECT</span> @@autocommit;</code></pre>



<p>但如果在进行操作之后，执行</p>
<pre><code class="hljs sql"><span class="hljs-keyword">COMMIT</span>;</code></pre>

<p>则无法再进行回滚（持久化）</p>
<h2 id="2、手动开启事务"><a href="#2、手动开启事务" class="headerlink" title="2、手动开启事务"></a>2、手动开启事务</h2><p>通过</p>
<pre><code class="hljs sql"><span class="hljs-keyword">BEGIN</span>;
或者
<span class="hljs-keyword">START</span> <span class="hljs-keyword">TRANSACTION</span>;</code></pre>

<p>可以手动开启事务</p>
<p><strong>插入数据前</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810204606.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>插入数据后</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">BEGIN</span>;
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> person <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">4</span>, <span class="hljs-string">'老六'</span>, <span class="hljs-number">7</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> person <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">5</span>, <span class="hljs-string">'老七'</span>, <span class="hljs-number">9</span>);
或者
<span class="hljs-keyword">START</span> <span class="hljs-keyword">TRANSACTION</span>;
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> person <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">4</span>, <span class="hljs-string">'老六'</span>, <span class="hljs-number">7</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> person <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">5</span>, <span class="hljs-string">'老七'</span>, <span class="hljs-number">9</span>);</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810204849.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>回滚后</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">ROLLBACK</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810204930.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="3、四大特征-ACID"><a href="#3、四大特征-ACID" class="headerlink" title="3、四大特征 ACID"></a>3、四大特征 ACID</h2><ul>
<li>原子性：事务是最小的单位，<strong>不可再分</strong></li>
<li>一致性：同一事务中的sql语句<strong>要么同时成功，要么同时失败</strong></li>
<li>隔离性：事务1和事务2之间具有隔离性</li>
<li>持久性：事物<strong>一旦结束</strong>（commit），就<strong>不可返回</strong>（rollback）</li>
</ul>
<h3 id="隔离性"><a href="#隔离性" class="headerlink" title="隔离性"></a>隔离性</h3><ul>
<li>事务的隔离级别<ul>
<li>读未提交 read uncommitted</li>
<li>读已提交 read committed</li>
<li>可以重复读 repeatable read</li>
<li>串行化 serializable</li>
</ul>
</li>
</ul>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200811132934.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>事务的并发问题<ul>
<li>脏读：事务A读取了事务B更新的数据，然后<strong>B回滚操作</strong>，那么<strong>A读取到的数据是脏数据</strong></li>
<li>不可重复读：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了<strong>更新并提交</strong>，<strong>导致事务A多次读取同一数据时，结果不一致</strong></li>
<li>幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读</li>
</ul>
</li>
</ul>
<p><strong>总结</strong>：不可重复读的和幻读很容易混淆，<strong>不可重复读侧重于修改，幻读侧重于新增或删除</strong></p>
<p>​            解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表</p>
<ul>
<li>查看数据库的隔离级别</li>
</ul>
<pre><code class="hljs sql">版本 5.x
<span class="hljs-keyword">SELECT</span> @@global.tx_isolation;

版本 8.0
<span class="hljs-keyword">SELECT</span> @@global.transaction_isolation;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200811132716.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>修改数据库的隔离级别</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">set</span> <span class="hljs-keyword">global</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> <span class="hljs-keyword">read</span> committed;</code></pre>


            </article>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/MySQL/">MySQL</a>
                    
                  </div>
                
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" target="_blank" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext row">
                  <div class="post-prev col-6">
                    
                    
                      <a href="/2020/08/16/MySQL%E9%AB%98%E7%BA%A7/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">MySQL高级</span>
                        <span class="visible-mobile">Previous</span>
                      </a>
                    
                  </div>
                  <div class="post-next col-6">
                    
                    
                      <a href="/2020/07/03/JVM%E5%AD%A6%E4%B9%A0/">
                        <span class="hidden-mobile">JVM</span>
                        <span class="visible-mobile">Next</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </div>
                </div>
              
            </div>

            
              <!-- Comments -->
              <div class="comments" id="comments">
                
                
  <div class="disqus" style="width:100%">
    <div id="disqus_thread"></div>
    <script type="text/javascript">
      function loadDisqus() {
        var disqus_config = function () {
          this.page.url = 'http://nyimac.gitee.io/2020/08/11/MySQL基础/';
          this.page.identifier = '/2020/08/11/MySQL基础/';
        };
        (function () {
          var d = document,
            s = d.createElement('script');
          s.src = '//' + '' + '.disqus.com/embed.js';
          s.setAttribute('data-timestamp', new Date());
          (d.head || d.body).appendChild(s);
        })();
      }
      createObserver(loadDisqus, 'disqus_thread');
    </script>
    <noscript>Please enable JavaScript to view the
      <a href="https://disqus.com/?ref_noscript" target="_blank" rel="nofollow noopener noopener">comments powered by Disqus.</a>
    </noscript>
  </div>


              </div>
            
          </div>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;TOC</p>
  <div id="tocbot"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    
  </main>

  
    <a id="scroll-top-button" href="#" role="button">
      <i class="iconfont icon-arrowup" aria-hidden="true"></i>
    </a>
  

  
    <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">Search</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">keyword</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
  

  

  

  <footer class="mt-5">
  <div class="text-center py-3">
    <div>
      <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a>
      <i class="iconfont icon-love"></i>
      <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener">
        <span>Fluid</span></a>
    </div>
    
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


    

    
  </div>
</footer>

<!-- SCRIPTS -->
<script  src="https://cdn.staticfile.org/jquery/3.4.1/jquery.min.js" ></script>
<script  src="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/js/bootstrap.min.js" ></script>
<script  src="/js/debouncer.js" ></script>
<script  src="/js/main.js" ></script>

<!-- Plugins -->


  
    <script  src="/js/lazyload.js" ></script>
  



  <script defer src="https://cdn.staticfile.org/clipboard.js/2.0.6/clipboard.min.js" ></script>
  <script  src="/js/clipboard-use.js" ></script>



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>





  <script  src="https://cdn.staticfile.org/tocbot/4.11.1/tocbot.min.js" ></script>
  <script>
    $(document).ready(function () {
      var boardCtn = $('#board-ctn');
      var boardTop = boardCtn.offset().top;

      tocbot.init({
        tocSelector: '#tocbot',
        contentSelector: 'article.markdown-body',
        headingSelector: 'h1,h2,h3,h4,h5,h6',
        linkClass: 'tocbot-link',
        activeLinkClass: 'tocbot-active-link',
        listClass: 'tocbot-list',
        isCollapsedClass: 'tocbot-is-collapsed',
        collapsibleClass: 'tocbot-is-collapsible',
        collapseDepth: 0,
        scrollSmooth: true,
        headingsOffset: -boardTop
      });
      if ($('.toc-list-item').length > 0) {
        $('#toc').css('visibility', 'visible');
      }
    });
  </script>



  <script  src="https://cdn.staticfile.org/typed.js/2.0.11/typed.min.js" ></script>
  <script>
    var typed = new Typed('#subtitle', {
      strings: [
        '  ',
        "MySQL基础&nbsp;",
      ],
      cursorChar: "_",
      typeSpeed: 70,
      loop: false,
    });
    typed.stop();
    $(document).ready(function () {
      $(".typed-cursor").addClass("h2");
      typed.start();
    });
  </script>



  <script  src="https://cdn.staticfile.org/anchor-js/4.2.2/anchor.min.js" ></script>
  <script>
    anchors.options = {
      placement: "right",
      visible: "hover",
      
    };
    var el = "h1,h2,h3,h4,h5,h6".split(",");
    var res = [];
    for (item of el) {
      res.push(".markdown-body > " + item)
    }
    anchors.add(res.join(", "))
  </script>



  <script  src="/js/local-search.js" ></script>
  <script>
    var path = "/local-search.xml";
    var inputArea = document.querySelector("#local-search-input");
    inputArea.onclick = function () {
      searchFunc(path, 'local-search-input', 'local-search-result');
      this.onclick = null
    }
  </script>



  <script  src="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.css" />

  <script>
    $('#post img:not(.no-zoom img, img[no-zoom]), img[zoom]').each(
      function () {
        var element = document.createElement('a');
        $(element).attr('data-fancybox', 'images');
        $(element).attr('href', $(this).attr('src'));
        $(this).wrap(element);
      }
    );
  </script>

















  
    <!-- Baidu Analytics -->
    <script defer>
      var _hmt = _hmt || [];
      (function () {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?ba41ec605b9b7320e120275462e4035b";
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
  

  

  

  

  

  





</body>
</html>
